// use crate::model::Fund;
use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Thing;

use crate::model::table;
use crate::model::table::Column;
use crate::model::table::Table;
use crate::model::table::KLINE_FLAT_TABLE;
use crate::model::FudaRecord;

use std::collections::HashSet;

use super::FUDB;
use super::FUHLC;

///
/// update or create a table record
///
/// @param table: table's basic information
///
pub async fn upsert_table(table: &table::Table) -> Result<Option<String>> {
    let id = table::Table::id_from(&table.name)?;

    let timestamp = FUHLC.new_timestamp().to_string();
    // Update a record with a specific ID
    let patch = FUDB.update(("table", &id))
        .patch(PatchOp::replace("/name", &table.name))
        .patch(PatchOp::replace("/title", &table.title))
        .patch(PatchOp::replace("/desc", &table.desc))
        .patch(PatchOp::replace("/last_updated", timestamp));

    let updated = patch.await.map_err(|err| anyhow!(err))?;

    Ok(updated)
}

pub async fn update_kline_flat_table_column(column: Column) -> Result<Column> {
    println!("(db::table::update_kline_flat_table_column) column={:?}", column);

    let table = find_table("KlineFlat").await?;

    let _rs = upsert_column(&table, &column).await?;

    Ok(column)
}

pub async fn upsert_column(table: &Table, column: &Column) -> Result<Option<FudaRecord>> {
    let id = Column::id_from(&table.name, &column.name)?;

    let mut to_update = false;
    let timestamp = FUHLC.new_timestamp().to_string();
    // Update a record with a specific ID
    let mut patch = FUDB.update(("column", id)).patch(PatchOp::replace("/last_updated", timestamp));

    patch = patch.patch(PatchOp::replace("/name", &column.name));
    if column.title.is_some() {
        patch = patch.patch(PatchOp::replace("/title", &column.title));
        to_update = true;
    }
    if column.visible.is_some() {
        patch = patch.patch(PatchOp::replace("/visible", &column.visible));
        to_update = true;
    }
    if column.desc.is_some() {
        patch = patch.patch(PatchOp::replace("/desc", &column.desc));
        to_update = true;
    }
    if to_update {
        let updated: Option<FudaRecord> = patch.await.map_err(|err| anyhow!(err))?;

        if let Some(thing) = &updated {
            let _rs_link = FUDB.query("RELATE $column->column_of->$table")
                .bind(("column", &thing.id))
                .bind(("table", &table.id)).await;

            println!("_rs_link ={:?}", _rs_link);
        }
        Ok(updated)
    } else {
        Ok(None)
    }
}

/// select a table & its columns
///
/// table_id: &str, e.g. SZ.KZZ.128136
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select_columns(table_name: &str) -> Result<Vec<Column>> {
    let id = Thing::from(("table", table_name));

    // SELECT *, (SELECT * FROM column WHERE klt=5 ORDER BY klts) as columns FROM table:⟨SZ.KZZ.128136⟩
    // Fetch column data within the specified date range using bind variables
    let table: Option<Table> = FUDB.query(
        r#"SELECT *, (SELECT * FROM column ) as columns FROM $id"#
    )
        .bind(("id", id)).await?
        .take(0)?;

    if let Some(Table { columns: Some(cols), .. }) = table {
        Ok(cols)
    } else {
        Ok(vec![])
    }
}

///
/// delete table by it's id , e.g. SH.STK.600519
///
pub async fn delete_table(table_id: &str) -> Result<Table> {
    let rs: Option<Table> = FUDB.delete(("table", table_id)).await?;
    match rs {
        Some(table) => Ok(table),
        None => Err(anyhow!(format!("Failed to delete, Table not found for {}", table_id))),
    }
}

/// find table
///
/// table_id: &str
///
///
pub async fn find_table(table_id: &str) -> Result<table::Table> {
    let id = Thing::from(("table", table_id));

    let table: Option<table::Table> = FUDB.query(
        r#"SELECT *, (SELECT * from column) as columns FROM $id"#
    )
        .bind(("id", id)).await?
        .take(0)?;

    match table {
        Some(tbl) => Ok(tbl),
        None => Err(anyhow!("Not found table for id={table_id}")),
    }
}
/// get KlineFlat Table & Columns by name, merge columns from [KLINE_FLAT_TABLE]
pub async fn get_kline_flat_table() -> Result<table::Table> {
    let result = find_table("KlineFlat").await;
    if result.is_err() {
        let _table_record = upsert_table(&KLINE_FLAT_TABLE).await?;
    }
    let table: Table = find_table("KlineFlat").await?;

    // merge columns from [KLINE_FLAT_TABLE] is not persisted on db yet
    let _rs = merge_and_save_columns(&table, &KLINE_FLAT_TABLE).await?;

    Ok(table)
}

///
/// Merge columns from source to target if target has no columns in source
///
/// @param target
/// @param source
///
async fn merge_and_save_columns(target: &Table, source: &Table) -> Result<()> {
    let mut existing_columns_set: HashSet<_> = HashSet::new();

    if let Some(columns) = &target.columns {
        // Create a HashSet to efficiently check for existing column names.
        existing_columns_set = columns
            .iter()
            .map(|c| &c.name)
            .collect();
    }

    if let Some(source_columns) = &source.columns {
        for column in source_columns {
            // If the column name is not in the set, plan to add the column.
            if !existing_columns_set.contains(&column.name) {
                let _record = upsert_column(target, column).await?;
            }
        }
    }

    Ok(())
}

#[cfg(test)]
mod test {
    use surrealdb::sql::Table;

    use crate::{
        db::{ self, table::get_kline_flat_table, FUHLC },
        model::table::{ self, KLINE_FLAT_TABLE },
    };

    #[tokio::test]
    async fn test_upsert_table() {
        let _ = db::init().await.unwrap();

        let table = &table::KLINE_FLAT_TABLE;

        // Update a table record with a specific id
        let res = db::table::upsert_table(&table).await;

        println!("res {:?}", res)
    }

    #[tokio::test]
    async fn test_find_table() {
        let _ = db::init().await.unwrap();

        let result = db::table::find_table("KlineFlat").await;

        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);
    }

    #[tokio::test]
    async fn test_upsert_columns() {
        let _ = db::init().await.unwrap();

        let _table_record = db::table::upsert_table(&KLINE_FLAT_TABLE).await.unwrap().unwrap();
        let table = db::table::find_table("KlineFlat").await.unwrap();

        for column in KLINE_FLAT_TABLE.columns.as_ref().unwrap() {
            let mut col = column.clone();
            col.visible = Some(true);
            let col_record = db::table::upsert_column(&table, &col).await.unwrap().unwrap();
            println!("column_record= {:?}\n", col_record);
        }
    }

    #[tokio::test]
    async fn test_select_columns() {
        let _ = db::init().await.unwrap();

        let result = db::table::select_columns("KlineFlat").await;
        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(columns) = &result {
            for (index, column) in columns.iter().enumerate() {
                if index > 3 {
                    break;
                }
                println!(
                    "column[{:02 }]= {:?},{},{:?}",
                    index,
                    column.id,
                    column.name,
                    column.visible
                );
            }
        }
    }

    #[tokio::test]
    async fn test_get_kline_flat_table() {
        let _ = db::init().await.unwrap();

        let result = get_kline_flat_table().await;

        println!("result={:?}", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(table::Table { columns: Some(cols), .. }) = result {
            for (index, column) in cols.iter().enumerate() {
                if index > 3 {
                    break;
                }
                println!(
                    "column[{:02 }]= {:?},{},{:?}",
                    index,
                    column.id,
                    column.name,
                    column.visible
                );
            }
        }
    }
}
